package zy.dao.shop.plan.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.shop.plan.MonthPlanDAO;
import zy.entity.shop.plan.T_Shop_MonthPlan;
import zy.entity.shop.plan.T_Shop_MonthPlan_Day;
import zy.util.CommonUtil;
import zy.util.StringUtil;

@Repository
public class MonthPlanDAOImpl extends BaseDaoImpl implements MonthPlanDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_shop_monthplan t");
		sql.append(" JOIN t_base_shop sp ON sp_code = mp_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND mp_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND mp_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("mp_year"))) {
			sql.append(" AND mp_year = :mp_year ");
		}
		if (StringUtil.isNotEmpty(params.get("mp_month"))) {
			sql.append(" AND mp_month = :mp_month ");
		}
		if (StringUtil.isNotEmpty(params.get("mp_ar_state"))) {
			sql.append(" AND mp_ar_state = :mp_ar_state ");
		}
		if (StringUtil.isNotEmpty(params.get("mp_shop_code"))) {
			sql.append(" AND mp_shop_code = :mp_shop_code ");
		}
		if (StringUtil.isNotEmpty(params.get("mp_number"))) {
			sql.append(" AND INSTR(mp_number,:mp_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Shop_MonthPlan> list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT mp_id,mp_number,mp_shop_code,mp_year,mp_month,mp_sell_money,mp_remark,mp_us_id,mp_ar_state,mp_ar_date,mp_maker,mp_sysdate,t.companyid,");
		sql.append(" sp_name AS shop_name");
		sql.append(" FROM t_shop_monthplan t");
		sql.append(" JOIN t_base_shop sp ON sp_code = mp_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND mp_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND mp_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("mp_year"))) {
			sql.append(" AND mp_year = :mp_year ");
		}
		if (StringUtil.isNotEmpty(params.get("mp_month"))) {
			sql.append(" AND mp_month = :mp_month ");
		}
		if (StringUtil.isNotEmpty(params.get("mp_ar_state"))) {
			sql.append(" AND mp_ar_state = :mp_ar_state ");
		}
		if (StringUtil.isNotEmpty(params.get("mp_shop_code"))) {
			sql.append(" AND mp_shop_code = :mp_shop_code ");
		}
		if (StringUtil.isNotEmpty(params.get("mp_number"))) {
			sql.append(" AND INSTR(mp_number,:mp_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		if (StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))) {
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY mp_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_MonthPlan.class));
	}
	
	@Override
	public T_Shop_MonthPlan load(Integer mp_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT mp_id,mp_number,mp_shop_code,mp_year,mp_month,mp_sell_money,mp_remark,mp_us_id,mp_ar_state,mp_ar_date,mp_maker,mp_sysdate,t.companyid,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = mp_shop_code AND sp.companyid = t.companyid LIMIT 1) AS shop_name");
		sql.append(" FROM t_shop_monthplan t");
		sql.append(" WHERE mp_id = :mp_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("mp_id", mp_id),
					new BeanPropertyRowMapper<>(T_Shop_MonthPlan.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public T_Shop_MonthPlan check(String shop_code, Integer year,Integer month, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT mp_id,mp_number,mp_ar_state");
		sql.append(" FROM t_shop_monthplan");
		sql.append(" WHERE 1=1");
		sql.append(" AND mp_year = :mp_year");
		sql.append(" AND mp_month = :mp_month");
		sql.append(" AND mp_shop_code = :mp_shop_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("mp_year", year).addValue("mp_month", month).addValue("mp_shop_code", shop_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Shop_MonthPlan.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Shop_MonthPlan check(String mp_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT mp_id,mp_number,mp_ar_state");
		sql.append(" FROM t_shop_monthplan");
		sql.append(" WHERE 1=1");
		sql.append(" AND mp_number = :mp_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("mp_number", mp_number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Shop_MonthPlan.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public void save(T_Shop_MonthPlan plan, List<T_Shop_MonthPlan_Day> days) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_shop_monthplan");
		sql.append(" (mp_number,mp_shop_code,mp_year,mp_month,mp_sell_money,mp_remark,mp_us_id,mp_ar_state,mp_ar_date,mp_maker,mp_sysdate,companyid)");
		sql.append(" VALUES");
		sql.append(" (:mp_number,:mp_shop_code,:mp_year,:mp_month,:mp_sell_money,:mp_remark,:mp_us_id,:mp_ar_state,:mp_ar_date,:mp_maker,:mp_sysdate,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(plan),holder);
		plan.setMp_id(holder.getKey().intValue());
		//计划到月明细
		for (T_Shop_MonthPlan_Day item : days) {
			item.setMpd_number(plan.getMp_number());
			item.setMpd_year(plan.getMp_year());
			item.setMpd_month(plan.getMp_month());
			item.setCompanyid(plan.getCompanyid());
		}
		sql.setLength(0);
		sql.append(" INSERT INTO t_shop_monthplan_day");
		sql.append(" (mpd_number,mpd_year,mpd_month,mpd_day,mpd_sell_money_plan,mpd_sell_money_pre,mpd_remark,companyid)");
		sql.append(" VALUES");
		sql.append(" (:mpd_number,:mpd_year,:mpd_month,:mpd_day,:mpd_sell_money_plan,:mpd_sell_money_pre,:mpd_remark,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(days.toArray()));
	}
	
	@Override
	public List<T_Shop_MonthPlan_Day> listDay(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT mpd_id,mpd_number,mpd_year,mpd_month,mpd_day,mpd_sell_money_plan,mpd_sell_money_pre,mpd_remark,companyid");
		sql.append(" FROM t_shop_monthplan_day t");
		sql.append(" WHERE 1=1");
		sql.append(" AND mpd_number = :number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("number", number).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Shop_MonthPlan_Day.class));
	}

	@Override
	public void updateApprove(T_Shop_MonthPlan plan) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_shop_monthplan");
		sql.append(" SET mp_ar_state=:mp_ar_state");
		sql.append(" ,mp_ar_date = :mp_ar_date");
		sql.append(" WHERE mp_id=:mp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(plan));
	}
	
	@Override
	public void del(String mp_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_monthplan");
		sql.append(" WHERE mp_number=:mp_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("mp_number", mp_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_shop_monthplan_day");
		sql.append(" WHERE mpd_number=:mp_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("mp_number", mp_number).addValue("companyid", companyid));
	}
	
}
